<?php

class Icl_Utility
{
	function Icl_Utility()
	{}
	
	function get_new_products_sql($current_product_id = '', $random = FALSE, $max = '')
	{
		global $wpdb;
		
		$sql = '';
		
		$new = $wpdb->get_results('SELECT `product_id` FROM `'.$wpdb->prefix . $this->table_prefix . 'products` WHERE `new` = 1 AND `new_start_date` <= "'.strtotime(date('Y-m-d')).'" AND `new_end_date` >= "'.strtotime(date('Y-m-d')).'"', ARRAY_A);
		if (is_array($new) && count($new))
		{
			$new_ids = array();
			foreach ($new as $nw)
			{
				$new_ids[] = $nw['product_id'];
			}
			
			$sql = 'SELECT DISTINCT ' . WPSC_TABLE_PRODUCT_LIST . '.id, name, ' . $wpdb->prefix . $this->table_prefix . 'products.simple_description, price, pnp, file, image, special, special_price, donation ';
			$sql .= 'FROM ' . WPSC_TABLE_PRODUCT_LIST . ' LEFT JOIN ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . ' ON ';
			$sql .= WPSC_TABLE_PRODUCT_LIST . '.id = ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . '.product_id ';
			$sql .= ' LEFT JOIN ' . $wpdb->prefix . $this->table_prefix . 'products ON ';
			$sql .= WPSC_TABLE_PRODUCT_LIST . '.id = ' . $wpdb->prefix . $this->table_prefix . 'products.product_id ';
			$sql .= 'WHERE publish = 1 AND active = 1 AND ' . WPSC_TABLE_PRODUCT_LIST . '.id IN ('.implode(',', $new_ids).') ';
			
			if ($current_product_id != '')
			{
				$sql .= 'AND ' . WPSC_TABLE_PRODUCT_LIST . '.id != ' . $current_product_id . ' ';
			}
			
			if ($random)
			{
				$sql .= 'ORDER BY RAND() ';
			}
			
			if ($max != '')
			{
				$sql .= 'LIMIT ' . $max;
			}
		}
		
		return $sql;
	}
	
	function stripNonText($text) {
		if ($text != '')
		{
			return preg_replace(array('@<script[^>]*?>.*?</script>@si', '@<[\/\!]*?[^<>]*?>@si'), array('', ''), $text);
		}
		
		return '';
	}

	function extractArgument($params, $name)
	{
		$ix = -1;
		$iy = -1;
		if (strlen($params) != 0)
		{
			$args = strtolower($params);
			$arg = strtolower($name).'=';
			$ix = strpos($args, $arg);
			if ($ix > 0)
			{
				$ix = $ix + strlen($arg);
				$iy = strpos(substr($args, $ix, strlen($args)), '&');
				if (!$iy)
				{
					$iy = strlen($args);
				}
			}
		}
		return $argument = ($ix > 0) ? substr($params, $ix, $iy) : '';
	}

	function getWeekRange($offset=0)
	{
		$start_date = '';
		$end_date = '';   
		$week = date('W');
		$week = $week - $offset;
		$date = date('Y-m-d');
	   
		$i = 0;
		while(date('W', strtotime("-$i day")) >= $week)
		{                       
			$start_date = date('Y-m-d', strtotime("-$i day"));
			$i++;       
		}   
		   
		list($yr, $mo, $da) = explode('-', $start_date);   
		$end_date = date('Y-m-d', mktime(0, 0, 0, $mo, $da + 6, $yr));
		
		return array($start_date, $end_date);
	}

	function getMonthRange($offset=0)
	{
		$start_date = '';
		$end_date = '';   
		$date = date('Y-m-d');
	   
		list($yr, $mo, $da) = explode('-', $date);
		$start_date = date('Y-m-d', mktime(0, 0, 0, $mo - $offset, 1, $yr));
	   
		$i = 2;
	   
		list($yr, $mo, $da) = explode('-', $start_date);
	   
		while(date('d', mktime(0, 0, 0, $mo, $i, $yr)) > 1)
		{
			$end_date = date('Y-m-d', mktime(0, 0, 0, $mo, $i, $yr));
			$i++;
		}
		
		return array($start_date, $end_date);
	}

	function get_sql_top_selling($range = array(), $max = 0)
	{	
		$sql = 'SELECT count(prodid) as total_download, prodid as id, b.name, LEFT(b.description, 80) as description, b.image ';
		$sql .= 'FROM ' . WPSC_TABLE_CART_CONTENTS . ' a, ' . WPSC_TABLE_PRODUCT_LIST . ' b ';
		$sql .= 'WHERE a.prodid = b.id AND b.publish = 1 AND b.active = 1 ';
		if (is_array($range) && count($range) > 0)
		{
			$sql .= 'AND (DATE_FORMAT(datetime, "%Y-%m-%d") BETWEEN "' . $range[0] . '" AND "' . $range[1] . '") ';
		}
		$sql .= 'GROUP BY prodid ORDER BY total_download DESC ';
		if ($max > 0)
		{
			$sql .= 'LIMIT ' . $max;
		}
		
		return $sql;
	}
	
	function get_products_by_category_sql($category_unid, $start = 0, $max = '')
	{
		global $wpdb;
		
		$category_unid = (intval($category_unid) > 0) ? $category_unid : 0;
		
		$sql = 'SELECT ' . WPSC_TABLE_PRODUCT_LIST . '.id, name, LEFT(' . WPSC_TABLE_PRODUCT_LIST . '.description, 80) as description, image ';
		$sql .= 'FROM ' . WPSC_TABLE_PRODUCT_LIST . ' LEFT JOIN ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . ' ON ';
		$sql .= WPSC_TABLE_PRODUCT_LIST . '.id = ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . '.product_id ';
		$sql .= 'WHERE publish = 1 AND active = 1 AND ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . '.category_id = ' . $category_unid . ' ';
		
		if ($max != '')
		{
			$sql .= 'LIMIT '.$start.','.$max;
		}
		
		return $sql;
	}

	function get_categories($limit_start = '', $limit_end = '', $show_empy_category = TRUE)
	{
		global $wpdb;
		
		$category_list = array();
		
		$sql = 'SELECT a.id, a.name, a.`nice-name` ';
		$sql .= 'FROM ';
		$sql .= WPSC_TABLE_PRODUCT_CATEGORIES . ' a, ' . WPSC_TABLE_CATEGORISATION_GROUPS . ' b ';
		if ($show_empy_category === FALSE)
		{
			$sql .= ', ' . WPSC_TABLE_ITEM_CATEGORY_ASSOC . ' c ';
			$sql .= ', ' . WPSC_TABLE_PRODUCT_LIST . ' d ';
		}
		$sql .= 'WHERE b.id = a.group_id AND LOWER(b.name) != "brands" AND `nice-name` NOT IN ("featured", "horizontal", "vertical", "newest") AND a.active = "1"';
		if ($show_empy_category === FALSE)
		{
			$sql .= 'AND a.id = c.category_id AND c.product_id = d.id AND d.active = 1 ';
		}
		$sql .= 'GROUP BY a.id ';
		if ($limit_end != '')
		{
			$sql .= 'LIMIT ' . $limit_start . ', ' . $limit_end;
		}
		
		$categories = $wpdb->get_results($sql, ARRAY_A);
		if($categories != null)
		{
			foreach($categories as $category)
			{
				$category_list[] = $category;
			}
			return $category_list;
		}
		
		return $category_list;
	}
	
	function get_categories_structure(&$categories_structure, $parent = 0)
	{
		global $wpdb;
		
		$sql = 'SELECT a.id, a.name, a.`nice-name` ';
		$sql .= 'FROM ';
		$sql .= WPSC_TABLE_PRODUCT_CATEGORIES . ' a, ' . WPSC_TABLE_CATEGORISATION_GROUPS . ' b ';
		$sql .= 'WHERE b.id = a.group_id AND LOWER(b.name) != "brands" AND a.active = "1" AND a.category_parent = ' . $parent .' ';
		$sql .= 'GROUP BY a.id';
		
		$categories = $wpdb->get_results($sql, ARRAY_A);
		if ($categories)
		{
			if (count($categories) > 0)
			{
				foreach($categories as $category)
				{
					$categories_structure[$parent][] = $category;
					
					$this->get_categories_structure($categories_structure, $category['id']);
				}
			}
		}
	}
	
	function get_categories_structure_by_child(&$categories_structure, $child = 0)
	{
		global $wpdb;
		
		$sql = 'SELECT a.id, a.name, a.`nice-name` ';
		$sql .= 'FROM ';
		$sql .= WPSC_TABLE_PRODUCT_CATEGORIES . ' a, ' . WPSC_TABLE_CATEGORISATION_GROUPS . ' b ';
		$sql .= 'WHERE b.id = a.group_id AND LOWER(b.name) != "brands" AND a.active = "1" AND a.category_parent = ' . $parent .' ';
		$sql .= 'GROUP BY a.id';
		
		$categories = $wpdb->get_results($sql, ARRAY_A);
		if ($categories)
		{
			if (count($categories) > 0)
			{
				foreach($categories as $category)
				{
					$categories_structure[$parent][] = $category;
					
					$this->get_categories_structure($categories_structure, $category['id']);
				}
			}
		}
	}

	function get_category($category)
	{
		global $wpdb;
		
		$category = (intval($category) > 0) ? $category : 0;
		
		$sql = 'SELECT id, name, `nice-name` FROM '. WPSC_TABLE_PRODUCT_CATEGORIES . ' ';
		$sql .= 'WHERE id = ' . $category;
		
		return $wpdb->get_row($sql, ARRAY_A);
	}
	
	function get_category_nicename($category, $with_parent = FALSE)
	{
		$current = $this->_get_category_nicename($category);
		
		if (is_array($current) && count($current) > 0)
		{
			$category_string = $current['nice-name'];
			if ($with_parent)
			{
				if (intval($current['category_parent']) > 0)
				{
					$category_string = $this->get_category_nicename($current['category_parent'], TRUE) . '/' . $category_string;
				}
			}
		}
		
		return $category_string;
	}
	
	function _get_category_nicename($category)
	{
		global $wpdb;
		
		$category = (intval($category) > 0) ? $category : 0;
		
		$sql = 'SELECT `nice-name`, `category_parent` FROM '. WPSC_TABLE_PRODUCT_CATEGORIES . ' ';
		$sql .= 'WHERE id = ' . $category . ' LIMIT 1';
		
		return $wpdb->get_row($sql, ARRAY_A);
	}

	function output_get_categories($product, $separator = ', ', $prefix = '', $suffix = '')
	{
		global $wpdb;
		
		$product = (intval($product) > 0) ? $product : 0;
		
		$sql = 'SELECT `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`id`,`'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`name` ';
		$sql .= 'FROM  ';
		$sql .= '`'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'` , `'.WPSC_TABLE_PRODUCT_CATEGORIES.'` ';
		$sql .= 'WHERE  ';
		$sql .= '`'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'`.`product_id` IN ('.$product.') AND `'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'`.`category_id` = `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`id` AND `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`active` IN("1")';
		$sql .= ' AND LOWER(name) NOT IN ("featured", "horizontal", "vertical")';
		
		$categories = $wpdb->get_results($sql, ARRAY_A);
		if($categories != null)
		{
			$category_list = array();
			foreach($categories as $category)
			{
				if (!empty($category['name']))
				{
					$new_prefix = sprintf($prefix, get_option('siteurl').'/products-page/'.$this->get_category_nicename($category['id'], TRUE));
					$category_list[] = $new_prefix.$category['name'].$suffix;
				}
			}
			return implode($separator, $category_list);
		}
		
		return '';
	}

	function output_get_categories_id($product, $separator = '')
	{
		global $wpdb;
		
		$product = (intval($product) > 0) ? $product : 0;
		
		$sql = 'SELECT `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`id` ';
		$sql .= 'FROM  ';
		$sql .= '`'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'` , `'.WPSC_TABLE_PRODUCT_CATEGORIES.'` ';
		$sql .= 'WHERE  ';
		$sql .= '`'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'`.`product_id` IN ('.$product.') AND `'.WPSC_TABLE_ITEM_CATEGORY_ASSOC.'`.`category_id` = `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`id` AND `'.WPSC_TABLE_PRODUCT_CATEGORIES.'`.`active` IN("1")';
		
		$categories = $wpdb->get_results($sql, ARRAY_A);
		if($categories != null)
		{
			$category_list = array();
			foreach($categories as $category)
			{
				$category_list[] = $category['id'];
			}
			return implode($separator, $category_list);
		}
		
		return '';
	}
	
	function get_parent_category_id($categories_structure, $child)
	{
		if (is_array($categories_structure) && count($categories_structure) > 0)
		{
			foreach($categories_structure as $parent => $categories)
			{
				foreach($categories as $category)
				{
					if ($category['id'] == $child)
					{
						return $parent;
					}
				}
			}
		}
		
		return 0;
	}
	
	function icl_dashboard_news_list()
	{
		$rss = @fetch_feed('http://feeds.feedburner.com/iclportfolio');
		
		if ( is_wp_error($rss) )
		{
			if ( is_admin() || current_user_can('manage_options') ){
				echo '<div class="rss-widget"><p>';
				printf(__('<strong>RSS Error</strong>: %s'), $rss->get_error_message());
				echo '</p></div>';
			}
		} elseif ( !$rss->get_item_quantity() ) {
			return false;
		} else {
			echo '<div class="rss-widget">';
			wp_widget_rss_output( $rss, array('items' => 5, 'show_summary' => TRUE, 'show_date' => TRUE) );
			echo '</div>';
		}
	}
	
	function create_post_string($method, $params, $sc) {
		$params['method'] = $method;
		if (!isset($params['v']))
		{
			$params['v'] = '1.0';
		}
		$post_params = array();
		foreach ($params as $key => &$val)
		{
			if (is_array($val)) $val = implode(',', $val);
			$post_params[] = $key.'='.urlencode($val);
		}
		$post_params[] = 'sig='.$this->generate_sig($params, $sc);
		return implode('&', $post_params);
	}
	
	function generate_sig($params_array, $sc) {
		$str = '';
		ksort($params_array);
		foreach ($params_array as $k=>$v)
		{
			$str .= "$k=$v";
		}
		$str .= $sc;
		return md5($str);
	}
	
	function ping($address, $method, $params, $sc)
	{
		$post_string = $this->create_post_string($method, $params, $sc);
		if (function_exists('curl_init'))
		{
			$ch = curl_init();
			curl_setopt($ch, CURLOPT_URL, $address);
			curl_setopt($ch, CURLOPT_POSTFIELDS, $post_string);
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
			curl_setopt($ch, CURLOPT_USERAGENT, 'ICL API Client 1.0 (curl) ' . phpversion());
			$result = curl_exec($ch);
			curl_close($ch);
		}
		else
		{
			$context =
				array('http' =>
					array('method' => 'POST',
						'header' => 'Content-type: application/x-www-form-urlencoded'."\r\n".
									'User-Agent: ICL API Client 1.0 (non-curl) '.phpversion()."\r\n".
									'Content-length: ' . strlen($post_string),
						'content' => $post_string));
			$contextid=stream_context_create($context);
			$sock=fopen($address, 'r', false, $contextid);
			if ($sock) {
				$result='';
				while (!feof($sock))
					$result.=fgets($sock, 4096);
				fclose($sock);
			}
		}
		return $result;
	}
	
	function icl_general()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/1.png" alt="Kelontong Premium Preview" /></a>';
	}

	function icl_menu()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/2.png" alt="Kelontong Premium Preview" /></a>';
	}

	function icl_categories()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/3.png" alt="Kelontong Premium Preview" /></a>';
	}

	function icl_products()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/4.png" alt="Kelontong Premium Preview" /></a>';
	}
	
	function icl_wishlist()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/5.png" alt="Kelontong Premium Preview" /></a>';
	}

	function icl_price_rules()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/6.png" alt="Kelontong Premium Preview" /></a>';
	}

	function icl_banner()
	{
		echo '<a href="http://portfolio.icreativelabs.com/wordpress/premium/kelontong/" target="_blank"><img src="http://ping.icreativelabs.com/previews/kelontong/7.png" alt="Kelontong Premium Preview" /></a>';
	}
}